﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

public partial class GUI_Pages_BabysitterRegistration : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DateTime myDateTime = DateTime.Now;
        string year = myDateTime.Year.ToString();
        int yr = int.Parse(year);

        ArrayList ayear = new ArrayList();
        int i;
        for (i = 1960; i <= yr; i++)
        {
            String n = i.ToString();
            ayear.Add(n);

        }
        byear.DataSource = ayear;
        byear.DataBind();

        ArrayList gradyear = new ArrayList();

        int j;
        for (j = 1960; j <= (yr + 10); j++)
        {
            String n = j.ToString();
            gradyear.Add(n);

        }

        bGradSchool1.DataSource = gradyear;
        bGradSchool1.DataBind();

        bGradSchool2.DataSource = gradyear;
        bGradSchool2.DataBind();

        bGradUni1.DataSource = gradyear;
        bGradUni1.DataBind();

        bGradUni2.DataSource = gradyear;
        bGradUni2.DataBind();
    }

    protected DateTime getBirthDate(String d, String m, String y)
    {
        Int32 day = Int32.Parse(d);
        Int32 month = Int32.Parse(m);
        Int32 year = Int32.Parse(y);
        DateTime dt = new DateTime(year, month, day);
        return dt;
    }

    protected bool conditionsRadio(RadioButtonList r)
    {
        bool b;
        if (r.SelectedValue == "Yes")
            b = true;
        else
            b = false;

        return b;
    }

    protected bool conditionsCheckitem(CheckBoxList c, int i)
    {
        bool b;
        if (c.Items[i].Selected)
            b = true;
        else
            b = false;

        return b;
    }

    protected bool conditionsCheck(CheckBox c)
    {
        bool b;
        if (c.Checked == true)
            b = true;
        else
            b = false;

        return b;
    }

    protected void AddEducation(int b, TextBox t, DropDownList d1, DropDownList d2, EventArgs e)
    {
        string connStr = ConfigurationManager.ConnectionStrings["CSConn"].ToString();
        SqlConnection conn = new SqlConnection(connStr);

        SqlCommand cmd1 = new SqlCommand("INSERT INTO SchoolsUniversities(SUName) VALUES(@name) SELECT SCOPE_IDENTITY()", conn);
        cmd1.CommandType = CommandType.Text;

        cmd1.Parameters.Add(new SqlParameter("@name", t.Text));

        conn.Open();
        int suID = Convert.ToInt32((decimal)cmd1.ExecuteScalar());
        conn.Close();

        SqlCommand cmd2 = new SqlCommand("INSERT INTO BSEducationalBackground(BSID, SUID, YearsCompleted, GraduationYear) " +
            "VALUES (@bsID, @suID, @years_completed, @grad_year)", conn);
        cmd2.CommandType = CommandType.Text;

        cmd2.Parameters.Add(new SqlParameter("@bsID", b));
        cmd2.Parameters.Add(new SqlParameter("@suID", suID));
        cmd2.Parameters.Add(new SqlParameter("@years_completed", Convert.ToInt32(d1.SelectedValue)));
        cmd2.Parameters.Add(new SqlParameter("@grad_year", Convert.ToInt32(d2.SelectedValue)));

        conn.Open();
        cmd2.ExecuteNonQuery();
        conn.Close();
    }

    protected void AddEmployment(int b, TextBox t1, TextBox t2, TextBox t3, EventArgs e)
    {
        string connStr = ConfigurationManager.ConnectionStrings["CSConn"].ToString();
        SqlConnection conn = new SqlConnection(connStr);

        SqlCommand cmd1 = new SqlCommand("INSERT INTO Employers(EName) VALUES(@e_name) SELECT SCOPE_IDENTITY()", conn);
        cmd1.CommandType = CommandType.Text;

        cmd1.Parameters.Add(new SqlParameter("@e_name", t1.Text));

        conn.Open();
        int empID = Convert.ToInt32((decimal)cmd1.ExecuteScalar());
        conn.Close();

        SqlCommand cmd2 = new SqlCommand("INSERT INTO BSEmploymentBackground(BSID, EDate, Duties, EmployerID) " +
        "VALUES (@bsID, @date, @duties, @empID)", conn);
        cmd2.CommandType = CommandType.Text;

        cmd2.Parameters.Add(new SqlParameter("@bsID", b));
        cmd2.Parameters.Add(new SqlParameter("@empID", empID));
        cmd2.Parameters.Add(new SqlParameter("@date", t2.Text));
        cmd2.Parameters.Add(new SqlParameter("@duties", t3.Text));

        conn.Open();
        cmd2.ExecuteNonQuery();
        conn.Close();
    }

    protected void AddHobby(int b, TextBox t)
    {
        string connStr = ConfigurationManager.ConnectionStrings["CSConn"].ToString();
        SqlConnection conn = new SqlConnection(connStr);

        SqlCommand cmd1 = new SqlCommand("INSERT INTO HobbiesSkills(HSName) VALUES(@h_name) SELECT SCOPE_IDENTITY()", conn);
        cmd1.CommandType = CommandType.Text;

        cmd1.Parameters.Add(new SqlParameter("@h_name", t.Text));

        conn.Open();
        int hsID = Convert.ToInt32((decimal)cmd1.ExecuteScalar());
        conn.Close();

        SqlCommand cmd2 = new SqlCommand("INSERT INTO HSs_BSs(BSID, HSID) VALUES (@bsID, @hsID)", conn);
        cmd2.CommandType = CommandType.Text;

        cmd2.Parameters.Add(new SqlParameter("@bsID", b));
        cmd2.Parameters.Add(new SqlParameter("@hsID", hsID));

        conn.Open();
        cmd2.ExecuteNonQuery();
        conn.Close();
    }

    protected void AddEmergencyContact(int a, TextBox t1, TextBox t2, TextBox t3, TextBox t4)
    {
        string connStr = ConfigurationManager.ConnectionStrings["CSConn"].ToString();
        SqlConnection conn = new SqlConnection(connStr);

        SqlCommand cmd3 = new SqlCommand("INSERT INTO EmergencyContacts (ECName, MobileNo, PhoneNo) " +
            "VALUES (@ec_name, @ec_mobile_num, @ec_phone_num) SELECT SCOPE_IDENTITY()", conn);
        cmd3.CommandType = CommandType.Text;

        cmd3.Parameters.Add(new SqlParameter("@ec_name", t1.Text));
        cmd3.Parameters.Add(new SqlParameter("@ec_mobile_num", t2.Text));
        cmd3.Parameters.Add(new SqlParameter("@ec_phone_num", t3.Text));

        conn.Open();
        int ecID = Convert.ToInt32((decimal)cmd3.ExecuteScalar());
        conn.Close();

        SqlCommand cmd5 = new SqlCommand("INSERT INTO EmergencyContacts_Accounts (AccountID, ECID, Relationship) " + 
            "VALUES (@acctID, @ecID, @relationship)", conn);
        cmd5.CommandType = CommandType.Text;

        cmd5.Parameters.Add(new SqlParameter("@acctID", a));
        cmd5.Parameters.Add(new SqlParameter("@ecID", ecID));
        cmd5.Parameters.Add(new SqlParameter("@relationship", t4.Text));

        conn.Open();
        cmd5.ExecuteNonQuery();
        conn.Close();
    }

    protected void AddScheduleSlot(int bsID, int dhID, string day)
    {
        string connStr = ConfigurationManager.ConnectionStrings["CSConn"].ToString();
        SqlConnection conn = new SqlConnection(connStr);

        int bssID = 0;
        SqlCommand cmd1 = new SqlCommand("INSERT INTO BSSchedule(BSID, S_Day) VALUES (@bsID, @day) SELECT SCOPE_IDENTITY()", conn);
        cmd1.CommandType = CommandType.Text;

        cmd1.Parameters.Add(new SqlParameter("@bsID", bsID));
        cmd1.Parameters.Add(new SqlParameter("@day", day));

        conn.Open();
        bssID = Convert.ToInt32((decimal)cmd1.ExecuteScalar());
        conn.Close();

        SqlCommand cmd2 = new SqlCommand("INSERT INTO DH_BSS(BSSID, DHID) VALUES(@bssID, @dhID)", conn);
        cmd2.CommandType = CommandType.Text;

        cmd2.Parameters.Add(new SqlParameter("@bssID", bssID));
        cmd2.Parameters.Add(new SqlParameter("@dhID", dhID));

        conn.Open();
        cmd2.ExecuteNonQuery();
        conn.Close();
    }

    protected void bSubmit_Click(object sender, EventArgs e)
    {
        string connStr = ConfigurationManager.ConnectionStrings["CSConn"].ToString();
        SqlConnection conn = new SqlConnection(connStr);

        SqlCommand cmd1 = new SqlCommand("INSERT INTO Accounts(AccountTypeID, UserName, Pass_word) VALUES (@acctID, @user_name, @pass_word) SELECT SCOPE_IDENTITY()", conn);
        cmd1.CommandType = CommandType.Text;

        cmd1.Parameters.Add(new SqlParameter("@acctID", 2));
        cmd1.Parameters.Add(new SqlParameter("@user_name", bUsername.Text));
        cmd1.Parameters.Add(new SqlParameter("@pass_word", bPassword.Text));

        conn.Open();
        int accID = Convert.ToInt32((decimal)cmd1.ExecuteScalar());
        conn.Close();

        SqlCommand cmd2 = new SqlCommand("INSERT INTO BabySitters(BSName, AccountID, DoB, NationalityID, Gender, CellNo, PhoneNo, EMail, BS_Address, Area, CityID, PostalCode, Experience, Essay, Allergies, Petconcern) " +
            "VALUES (@full_name, @acctID, @DoB, @nationality_id, @gender, @cellNo, @phoneNo, @email, @address, @area, @cityID, @postalCode, @experience, @essay, @allg, @pet) " + 
            "SELECT SCOPE_IDENTITY ()", conn);
        cmd2.CommandType = CommandType.Text;

        String name = bFirstName.Text + " " + bLastName.Text;

        cmd2.Parameters.Add(new SqlParameter("@full_name", name));
        cmd2.Parameters.Add(new SqlParameter("@acctID", accID));
        cmd2.Parameters.Add(new SqlParameter("@DoB", getBirthDate(bday.SelectedValue, bmonth.SelectedValue, byear.SelectedValue)));
        cmd2.Parameters.Add(new SqlParameter("@nationality_id", Int32.Parse(bNationalities.SelectedValue)));
        cmd2.Parameters.Add(new SqlParameter("@gender", Convert.ToChar(bGender.SelectedValue)));
        cmd2.Parameters.Add(new SqlParameter("@cellNo", bMobileNo.Text));
        cmd2.Parameters.Add(new SqlParameter("@phoneNo", bPhoneNo.Text));
        cmd2.Parameters.Add(new SqlParameter("@email", bEmail.Text));
        cmd2.Parameters.Add(new SqlParameter("@address", bAddress.Text));
        cmd2.Parameters.Add(new SqlParameter("@area", bArea.Text));
        cmd2.Parameters.Add(new SqlParameter("@cityID", 1));
        cmd2.Parameters.Add(new SqlParameter("@postalCode", bPostalCode.Text));
        cmd2.Parameters.Add(new SqlParameter("@experience", bExperience.Text));
        cmd2.Parameters.Add(new SqlParameter("@essay", bEssay.Text));
        cmd2.Parameters.Add(new SqlParameter("@allg", bAllergies.Text));
        cmd2.Parameters.Add(new SqlParameter("@pet", bPetConcern.Text));

        conn.Open();
        int bsID = Convert.ToInt32((decimal)cmd2.ExecuteScalar());
        conn.Close();

        if (bSchool1.Text != "")
        {
            AddEducation(bsID, bSchool1, bYearsSchool1, bGradSchool1, e);
        }
        if (bSchool2.Text != "")
        {
            AddEducation(bsID, bSchool2, bYearsSchool2, bGradSchool2, e);
        }
        if (bUniversity1.Text != "")
        {
            AddEducation(bsID, bUniversity1, bYearsUni1, bGradUni1, e);
        }
        if (bUniversity2.Text != "")
        {
            AddEducation(bsID, bUniversity2, bYearsUni2, bGradUni2, e);
        }
        if (bEmployer1.Text != "")
        {
            AddEmployment(bsID, bEmployer1, bEmpDate1, bEmpDuties1, e);
        }
        if (bEmployer2.Text != "")
        {
            AddEmployment(bsID, bEmployer2, bEmpDate2, bEmpDuties2, e);
        }


        for (int k = 0; k < bLanguages.Items.Count; k++)
        {
            if (bLanguages.Items[k].Selected)
            {
                int x = int.Parse(bLanguages.Items[k].Value);
                SqlCommand cmd3 = new SqlCommand("INSERT INTO BabySitters_Languages(BSID, LanguageID) VALUES (@bsID, @languageID)", conn);
                cmd3.CommandType = CommandType.Text;

                cmd3.Parameters.Add(new SqlParameter("@bsID", bsID));
                cmd3.Parameters.Add(new SqlParameter("@languageID", x));

                conn.Open();
                cmd3.ExecuteNonQuery();
                conn.Close();
            }
        }

        if (bHobby1.Text != "")
        {
            AddHobby(bsID, bHobby1);
        }
        if (bHobby2.Text != "")
        {
            AddHobby(bsID, bHobby2);
        }
        if (bHobby3.Text != "")
        {
            AddHobby(bsID, bHobby3);
        }
        if (bHobby4.Text != "")
        {
            AddHobby(bsID, bHobby4);
        }
        if (bHobby5.Text != "")
        {
            AddHobby(bsID, bHobby5);
        }
        if (bHobby6.Text != "")
        {
            AddHobby(bsID, bHobby6);
        }

        SqlCommand cmd6 = new SqlCommand("INSERT INTO BSWorkConditions(BSID, clubs, cinemas, otherEvents, withparentsathome, withparentsatclub, withmaidathome, withmaidatclub, aloneathome, aloneatclub, daycaretwosix, daycaresixtwelve, nightcare, workoffsched, hoursaweek, hoursaday) " +
        "VALUES (@bsID, @clubs, @cinemas, @otherEvents, @withparentsathome, @withparentsatclub, @withmaidathome, @withmaidatclub, @aloneathome, @aloneatclub, @daycaretwosix, @daycaresixtwelve, @nightcare, @workoffsched, @hrsweek, @hrsday)", conn);
        cmd6.CommandType = CommandType.Text;

        cmd6.Parameters.Add(new SqlParameter("@bsID", bsID));
        cmd6.Parameters.Add(new SqlParameter("@clubs", conditionsRadio(bWorkConditionRadio1)));
        cmd6.Parameters.Add(new SqlParameter("@cinemas", conditionsRadio(bWorkConditionRadio2)));
        cmd6.Parameters.Add(new SqlParameter("@otherEvents", conditionsRadio(bWorkConditionRadio3)));
        cmd6.Parameters.Add(new SqlParameter("@withparentsathome", conditionsCheckitem(bWorkConditionCheck, 0)));
        cmd6.Parameters.Add(new SqlParameter("@withparentsatclub", conditionsCheckitem(bWorkConditionCheck, 3)));
        cmd6.Parameters.Add(new SqlParameter("@withmaidathome", conditionsCheckitem(bWorkConditionCheck, 1)));
        cmd6.Parameters.Add(new SqlParameter("@withmaidatclub", conditionsCheckitem(bWorkConditionCheck, 4)));
        cmd6.Parameters.Add(new SqlParameter("@aloneathome", conditionsCheckitem(bWorkConditionCheck, 2)));
        cmd6.Parameters.Add(new SqlParameter("@aloneatclub", conditionsCheckitem(bWorkConditionCheck, 5)));
        cmd6.Parameters.Add(new SqlParameter("@daycaretwosix", conditionsCheck(DayCareB)));
        cmd6.Parameters.Add(new SqlParameter("@daycaresixtwelve", conditionsCheck(DayCareA)));
        cmd6.Parameters.Add(new SqlParameter("@nightcare", conditionsCheck(NightCareService)));
        cmd6.Parameters.Add(new SqlParameter("@workoffsched", conditionsCheck(bNotify)));
        cmd6.Parameters.Add(new SqlParameter("@hrsweek", int.Parse(bHoursPerWeek.SelectedValue)));
        cmd6.Parameters.Add(new SqlParameter("@hrsday", int.Parse(bHoursPerDay.SelectedValue)));

        conn.Open();
        cmd6.ExecuteNonQuery();
        conn.Close();

        AddEmergencyContact(accID, bEmergency1, bEmergencyMob1, bEmergencyPhone1, bEmergencyRel1);
        if (bEmergency2.Text != "")
        {
            AddEmergencyContact(accID, bEmergency2, bEmergencyMob2, bEmergencyPhone2, bEmergencyRel2);
        }

        for (int i = 0; i < bSchedule.Items.Count; i++)
        {
            if (bSchedule.Items[i].Selected)
            {
                string[] a = bSchedule.Items[i].Value.Split(',');
                int dhID = int.Parse(a[0]);
                if (a[1] == "1")
                {
                    AddScheduleSlot(bsID, dhID, "Sunday");
                }
                if (a[1] == "2")
                {
                    AddScheduleSlot(bsID, dhID, "Monday");
                }
                if (a[1] == "3")
                {
                    AddScheduleSlot(bsID, dhID, "Tuesday");
                }
                if (a[1] == "4")
                {
                    AddScheduleSlot(bsID, dhID, "Wednesday");
                }
                if (a[1] == "5")
                {
                    AddScheduleSlot(bsID, dhID, "Thursday");
                }
                if (a[1] == "6")
                {
                    AddScheduleSlot(bsID, dhID, "Friday");
                }
                if (a[1] == "7")
                {
                    AddScheduleSlot(bsID, dhID, "Saturday");
                }
            }
        }

        Response.Redirect("http://www.cairositters.com/GUI/Pages/BabysitterRegistrationSuccessful.aspx");
        //Response.Redirect("http://localhost:60070/CSWebSite_v1.0/GUI/Pages/BabysitterRegistrationSuccessful.aspx");
    }
}